using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;

namespace DAO
{
   public  class NhanVienDAO
    {
       public List<NhanVienDTO> laydanhsachnhanvien_DAO()
       {
           List<NhanVienDTO> list = new List<NhanVienDTO>();
           string sql = "select * from NhanVien";
           DataTable dt = DataProvider.ExcuteQuery(sql);
           if (dt.Rows.Count > 0)
           {
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   DataRow dr = dt.Rows[i];

                   int manhanvien = int.Parse(dr["MaNhanVien"].ToString());
                   string tennhanvien = dr["HoTen"].ToString();
                   string diachi = dr["DiaChi"].ToString ();
                   DateTime ngaysinh =DateTime.Parse ( dr["NgaySinh"].ToString ());
                   string dienthoai = dr["DienThoai"].ToString ();
                   int bangcap = int.Parse(dr["BangCap"].ToString ());
                   int bophan = int.Parse(dr["BoPhan"].ToString ());
                   int chucvu = int.Parse(dr["ChucVu"].ToString ());

                   NhanVienDTO  nv = new NhanVienDTO ();

                   nv.Manhanvien = manhanvien;
                   nv.Hoten = tennhanvien ;
                   nv.Diachi = diachi;
                   nv.Ngaysinh = ngaysinh;
                   nv.Dienthoai = dienthoai;
                   nv.Bangcap = bangcap;
                   nv.Chucvu = chucvu;
                   nv.Bophan = bophan;

                   list.Add(nv);
               }
           }
           return list;
       }
       public  void themnhanvien_DAO(NhanVienDTO nv)
       {
           string sql = "insert into NhanVien (HoTen,DiaChi,NgaySinh,DienThoai,BangCap,ChucVu,BoPhan) values ";
           sql += "('"+nv.Hoten+"','"+nv.Diachi+"','"+nv.Ngaysinh+"','"+nv.Dienthoai+"',"+nv.Bangcap+","+nv.Chucvu+","+nv.Bophan+")";
           DataProvider.ExecutenonQuery(sql);
       }

       public void xoanhanvien_DAO(int manhanvien)
       {
           string sql = "delete from NhanVien where MaNhanVien =" + manhanvien;
           DataProvider.ExecutenonQuery(sql);
       }

       public List<NhanVienDTO> timnhanvientheoten_DAO(string tennhanvien1)
       {
           List<NhanVienDTO> list = new List<NhanVienDTO>();
           string sql = "select * from NhanVien where HoTen Like '%"+tennhanvien1+"%'";
           DataTable dt = DataProvider.ExcuteQuery(sql);
           if (dt.Rows.Count > 0)
           {
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   DataRow dr = dt.Rows[i];

                   int manhanvien = int.Parse(dr["MaNhanVien"].ToString());
                   string tennhanvien = dr["HoTen"].ToString();
                   string diachi = dr["DiaChi"].ToString();
                   DateTime ngaysinh = DateTime.Parse(dr["NgaySinh"].ToString());
                   string dienthoai = dr["DienThoai"].ToString();
                   int bangcap = int.Parse(dr["BangCap"].ToString());
                   int bophan = int.Parse(dr["BoPhan"].ToString());
                   int chucvu = int.Parse(dr["ChucVu"].ToString());

                   NhanVienDTO nv = new NhanVienDTO();

                   nv.Manhanvien = manhanvien;
                   nv.Hoten = tennhanvien;
                   nv.Diachi = diachi;
                   nv.Ngaysinh = ngaysinh;
                   nv.Dienthoai = dienthoai;
                   nv.Bangcap = bangcap;
                   nv.Chucvu = chucvu;
                   nv.Bophan = bophan;

                   list.Add(nv);
               }
           }
           return list;
       }

       public NhanVienDTO LayNhanVienTheoID(int id)
       {
           NhanVienDTO nvDTO = new NhanVienDTO();
           string sql = "select * from NhanVien where MaNhanVien=" + id.ToString();
           DataTable dt = DataProvider.ExcuteQuery(sql);
           if (dt.Rows.Count < 1)
               return null;
           nvDTO.Manhanvien = id;
           nvDTO.Hoten = dt.Rows[0]["HoTen"].ToString();
           nvDTO.Diachi = dt.Rows[0]["DiaChi"].ToString();
           nvDTO.Ngaysinh = DateTime.Parse(dt.Rows[0]["NgaySinh"].ToString());
           nvDTO.Dienthoai = dt.Rows[0]["DienThoai"].ToString();
           nvDTO.Bangcap = int.Parse(dt.Rows[0]["BangCap"].ToString());
           nvDTO.Bophan = int.Parse(dt.Rows[0]["BoPhan"].ToString());
           nvDTO.Chucvu = int.Parse(dt.Rows[0]["ChucVu"].ToString());
           return nvDTO;
       }

       public static DataTable LayDSNhanVienTheoBoPhan_DAO(int boPhan)
       {
           DataTable dt = new DataTable();
           string sql = "select*from NhanVien where BoPhan=" + boPhan.ToString();
           dt = DataProvider.ExcuteQuery(sql);
           return dt;
       }
    }
    
}
